# -*- coding: utf-8 -*-
import json

def getPersonCertList(request):
    #1数据
    request.cursor_standard.execute(request.formatSql('''
        select 
            person_apt.person_id,
            person_apt.filing,
            person_apt.cert_num,
            person_apt.registered_num,
            person_apt.issued_by,
            person_apt.issued_date,
            person_apt.expiry_date,
            dict_person_apt.*,
            dict_person_apt_level.level_name
        from standard.person_apt
        left join dict_person_apt on person_apt.cert_dict_id = dict_person_apt.cert_dict_id
        left join dict_person_apt_level on person_apt.level_id = dict_person_apt_level.level_id
        where (  '${person_id}' = '' or person_id like '%${person_id}%'   )
        and ('%${apt_dict_id}%' = '' or  person_apt.cert_dict_id = '${apt_dict_id}')
        limit ${start} , ${rows} 
    '''))
    rows = request.cursor_standard.fetchall()
    #2页码
    request.cursor_standard.execute(request.formatSql('''
        select
            count(*) as total
        from standard.person_apt
        left join standard.dict_person_apt on person_apt.cert_dict_id = dict_person_apt.cert_dict_id
        where (  '${person_id}' = '' or person_id like '%${person_id}%'   )
        and ('%${apt_dict_id}%' = '' or  person_apt.cert_dict_id = '${apt_dict_id}')
    '''))
    total = request.cursor_standard.fetchone()['total']
    return json.dumps({'rows':rows,'total':total}, ensure_ascii=False)

